﻿using System.Data.Odbc;
using personalplaner.business.common;
using personalplaner.core.sql;
using System.Collections.Generic;
using System;
using personalplaner.core.odbc;
using personalplaner.business.enums;
using personalplaner.core.errorhandling;

namespace personalplaner.dataprovider.common
{
	public partial class DatabaseConnectorService
	{
        /// <summary>
        /// tests the settings for connecting to the database server
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
        public bool TestUserConnection(LoginData login)
        {
            using (var con = new OdbcConnection(string.Format("DSN={0};UID={1};PWD={2};Database={3};", login.DSN, login.UserName, login.Password, login.Database)))
            {
                try
                {
                    con.Open();
                    return true;
                }
                catch (Exception)
                {
                }

                return false;
            }
        }

        /// <summary>
        /// tests the connection and if the database is a personalplaner database
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
		public bool TestDatabaseConnection(LoginData login)
		{
			if (login == null)
				return false;

			using(var settings = new DatabaseSettings())
			{
				bool connectionOK = true;

				settings.ConnetionString = string.Format("DSN={0};UID={1};PWD={2};Database={3};", login.DSN, login.UserName, login.Password, login.Database);

				try
				{
					var dv = SimpleQuerry.ExecuteForDataTable(settings, "select 1 from sysobjects where name = 'PrsVerwaltung' and type = 'U'");
					if (dv.Rows.Count > 0)
						connectionOK = true;
					else
						connectionOK = false;
				}
				catch (OdbcException)
				{
					connectionOK = false;
				}

				return connectionOK;
			}
		}

		/// <summary>
		/// Checks and compares the database version with the api version
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="version"></param>
		/// <returns></returns>
		public enVersionCompare CompareDatabaseVersion(DatabaseSettings settings, Version version)
		{
			var dv = SimpleQuerry.ExecuteForDataTable(settings,
						"select Version from PrsVerwaltung where VerwaltungID = (select MAX(VerwaltungID) from PrsVerwaltung)");
			if (dv.Rows.Count <= 0)
				return enVersionCompare.NoVersion; //no version

			//string[] dbVers = dv[0].Row[0].ToString().Split('.');
			var dbVersion = new Version(dv.Rows[0][0].ToString());
			if (dbVersion > version)
				return enVersionCompare.ApplicationVersionIsSmaller;

			if (dbVersion < version)
				return enVersionCompare.DatabseVersionIsSmaller;

			return enVersionCompare.SameVersion;
		}

		/// <summary>
		/// Gets the version of the personalplaner database
		/// </summary>
		/// <param name="settings"></param>
		/// <returns></returns>
		public string GetDatabaseVersion(DatabaseSettings settings)
		{
			var dv = SimpleQuerry.ExecuteForDataTable(settings,
													  "select Version from PrsVerwaltung where VerwaltungID = (select MAX(VerwaltungID) from PrsVerwaltung)");

			if (dv.Rows.Count <= 0)
				return "0.0.0.0"; //no version

			return dv.Rows[0].ToString();
		}

		//public bool CheckIfOpenYearExists(DatabaseSettings settings)
		//{
		//    var dv = SimpleQuerry.ExecuteForDataTable(settings, "select JahrID, Jahr from PlanungsJahr where Status = 1");

		//    if (dv.Rows.Count > 0 && !string.IsNullOrEmpty(dv.Rows[0][0].ToString()))
		//    {
		//        return true;
		//    }

		//    return false;
		//}

		//public int GetActiveYearId(DatabaseSettings settings, string mandid)
		//{
		//    var table = SimpleQuerry.ExecuteForDataTable(settings, string.Format("select jahrid = JahrID, Jahr from PlanungsJahr where Status = 1 and mandid = '{0}'", mandid));

		//    if (table.Rows.Count > 0 && table.Columns.Contains("jahrid"))
		//    {
		//        try
		//        {
		//            return table.Rows[0].Field<int>("jahrid");
		//        }
		//        catch (Exception)
		//        {
		//            return 0;
		//        }
		//    }

		//    return 0;
		//}

		//public int GetActiveYear(DatabaseSettings settings, string mandid)
		//{
		//    var table = SimpleQuerry.ExecuteForDataTable(settings, string.Format("select jahr = Jahr from PlanungsJahr where Status = 1 and mandid = '{0}'", mandid));
		//}
        public bool IsLoginValid(string odbcSource, string user, string password)
        {
            string sqlStr = "select * from sys.server_principals u " +
                "inner join sys.server_role_members m on m.member_principal_id = u.principal_id " +
                "inner join sys.server_principals g on g.principal_id = m.role_principal_id and g.type = 'R' and g.name in ('sysadmin','serveradmin') " +
                "where u.type= 'S' and u.is_disabled = 0 and u.name = '" + user + "'";

            string connectionStr = string.Format("DSN={0};UID={1};PWD={2};Database=master;", odbcSource, user, password);
            var dt = SimpleQuerry.ExecuteForDataTable(new DatabaseSettings { ConnetionString = connectionStr }, sqlStr);

            if (dt == null || dt.Rows.Count <= 0)
                return false;
            return true;
        }

		public IEnumerable<string> GetOdbcList()
		{
			var lst = new List<string>();
			try
			{
				// USER-DSN-Verbindung
				foreach (var dns in ODBCManager.UserDSNList)
				{
					// Nur SQL Server in die Liste einbinden
					if (dns.DriverName == "SQL Server")
						lst.Add(dns.DSNName);
				}
			}
			catch (Exception)
			{
                ActionLogger.LoggMessage(MessageType.ODBC_ERROR, "Keine USER-DSN-Verbindung vorhanden!");
			}

			try
			{
				// SYSTEM-DSN-Verbindung
				foreach (var dns in ODBCManager.SystemDSNList)
				{
					if (dns.DriverName == "SQL Server")
						lst.Add(dns.DSNName);
				}
			}
			catch (Exception)
			{
                ActionLogger.LoggMessage(MessageType.ODBC_ERROR, "Keine SYSTEM-DSN-Verbindung vorhanden!");
			}

			return lst;
		}

		public IEnumerable<string> GetDatabaseList(LoginData login, bool all = false)
		{
			var settings = new DatabaseSettings();
			settings.ConnetionString = string.Format("DSN={0};UID={1};PWD={2};Database=master;", login.DSN, login.UserName, login.Password);

			var table = SimpleQuerry.ExecuteForDataTable(settings, "sp_helpdb");

			if (table.Rows.Count == 0)
				return null;

			try
			{
				var databases = m_databaseData(table);
                if(all)
                    return databases;

                var prsDbs = new List<string>();

                foreach (var db in databases)
                {
                    settings.ConnetionString = string.Format("DSN={0};UID={1};PWD={2};Database={3};", login.DSN, login.UserName, login.Password, db);
                    var tmp = SimpleQuerry.ExecuteForDataTable(settings, "select convert(char(3), count(*)) as retval from sysobjects where name='PrsVerwaltung'");
                    if (m_databaseIsPersonalplaner(tmp))
                        prsDbs.Add(db);
                }

                return prsDbs;
			}
			catch (Exception e)
			{
                ActionLogger.LoggMessage(MessageType.ODBC_ERROR, "GetDatabseList caused an error");
				throw new Exception("GetDatabseList caused an error", e);
			}
		}

	}
}
